package edu.hhu.basic.dao;
import java.util.ArrayList;
import java.util.List;

import com.jfinal.plugin.activerecord.Db;
import com.jfinal.plugin.activerecord.Page;
import com.jfinal.plugin.activerecord.Record;

public class BasicDao {
    //根据所选的井号查询
	public Page<Record> Column(String tableName, int page, String para) {
		Page<Record> list = null;
		//if ("choice".equals(tableName)) {
		//	list = Db.use("mysql").paginate(page, 20, "select *",
				//	"from cjsctjtemp");
		//} else {
			System.out.println("haha");
			String[] strPara = para.split(",");
			String sql=null;
			String select="select *";
			/*if("fxhysj".equals(tableName)){
				 sql = "from " + tableName + " where wellcode in (select wellcode from cjkj where wellcode in(";
				for (int i = 0; i < strPara.length; i++) {
					sql += "?";
					if (i < strPara.length - 1) {
						sql += ",";
					}
				}
				sql += "))";
			}else if("dtsjtj".equals(tableName)){
				 sql = "from " + tableName +" "+ "JOIN cjkj on dtsjtj.wellcode=cjkj.wellcode where dtsjtj.wellcode in (select wellcode from cjkj where wellcode in(";
				 select="select dtsjtj.*,cjkj.wellname wellname";
					for (int i = 0; i < strPara.length; i++) {
						sql += "?";
						if (i < strPara.length - 1) {
							sql += ",";
						}
					}
					sql += "))";
					
			}else{*/
			sql = "from " + tableName + " where wellcode in(";
			for (int i = 0; i < strPara.length; i++) {
				sql += "?";
				if (i < strPara.length - 1) {
					sql += ",";
				}
			}
			sql += ")";
			System.out.println(sql);
			list = Db.use("mysql").paginate(page, 20, select, sql, strPara);
		return list;
	}
//基础查询
	public List<Record> BasicQuery(String para,String table){
		String[] split = para.split(",");
		//System.out.println("hheh"+split[0]);
		System.out.println(split.length);
		for (int i = 0; i < split.length; i++) {
			if(" ".equals(split[i])){
			split[i] = "%";
			}else{
			split[i] += "%";
			}
		}
		System.out.println(table);
		List<Record> list = null;
	    if ("jtjcsj".equals(table)) {
			String sql = "select * from " + " " + table + " "
					+ "where 1=1 and wellname like ? and elevation like ? and depth like ? and stratumpressure like ?";
			System.out.println(sql);
			list = Db.use("mysql").find(sql, split);
		} else if ("mccjsj".equals(table)) {
			String sql = "select * from " + " " + table + " "
					+ "where 1=1 and wellname like ? and Lithologycode like ? and layered like ?";
			System.out.println(sql);
			list = Db.use("mysql").find(sql, split);
		} else if ("mcsjsj".equals(table)) {
			String sql = "select * from " + " " + table + " "
					+ "where 1=1 and wellname like ? and NCS like ? and temperature like ? and penetrance like ?";
			System.out.println(sql);
			list = Db.use("mysql").find(sql, split);
		} else if ("fxhysj".equals(table)) {
			String sql = "select * from " + " " + table + " "
					+ "where 1=1 and wellname like ? and pressure like ? and TGC like ?";
			System.out.println(sql);
			list = Db.use("mysql").find(sql, split);
		} else if ("dtsjtj".equals(table)) {
			String sql = "select * from " + " " + table + " "
					+ "where 1=1 and TCRQ like ? and JQRQ like ?";
			/*String sql = "select dtsjtj.*,cjkj.wellname wellname from " + " " + table + " "+" join cjkj"+" "
					+ "where 1=1 and  TCRQ like ? and JQRQ like ?";*/
			System.out.println(sql);
			list = Db.use("mysql").find(sql, split);
		}  else if ("pcdtsjtrue".equals(table)) {
			String sql = "select * from " + " " + table + " "
					+ "where 1=1 and wellname like ? and SCRQ like ? and QQ like ? and QL like ? ";
			System.out.println(sql);
			list = Db.use("mysql").find(sql, split);
		} else if ("ylsgsj".equals(table)) {
			String sql = "select * from " + " " + table + " "
					+ "where 1=1 and wellname like ? and address like ? and PL like ? and SFCG like ?";
			System.out.println(sql);
			list = Db.use("mysql").find(sql, split);
		} else if ("mcddbcjsj".equals(table)) {
			String sql = "select * from " + " " + table + " "
					+ "where 1=1 and wellname like ? and Baffletcode like ? and Lithologycode like ?";
			System.out.println(sql);
			list = Db.use("mysql").find(sql, split);
		}
		return list;
	}
	public Page<Record> AdvanceQuery(String para,int pageno){
		String[] split = para.split(",");
		System.out.println(split.length);
		String sql=null;
		Page<Record> page=null;
		if(split.length==11&&"".equals(split[7])){
			   sql = "from (select a.wellcode,a.wellname,a.elevation,a.depth,a.totalthickness,a.netthickness,a.stratumpressure,b.SL,b.PLYL,b.PJSB from jtjcsj a inner join ylsgsj b on a."+split[1]+split[2]+"? and a.wellcode=b.wellcode ) c inner join pcdtsjtrue d on c.wellcode=d.wellcode";
			   System.out.println(sql);
			   page = Db.use("mysql").paginate(pageno, 20, "select c.wellcode,c.wellname,c.elevation,c.depth,c.totalthickness,c.netthickness,c.stratumpressure,c.SL,c.PLYL,c.PJSB,d.SCRQ,d.QQ,d.QL",sql,split[3]);
		}else if(split.length==11&&"".equals(split[3])){
		       sql = "from (select a.wellcode,a.wellname,a.elevation,a.depth,a.totalthickness,a.netthickness,a.stratumpressure,b.SL,b.PLYL,b.PJSB from jtjcsj a inner join ylsgsj b on b."+split[5]+split[6]+"? and a.wellcode=b.wellcode ) c inner join pcdtsjtrue d on c.wellcode=d.wellcode";
			   System.out.println(sql);
		       page = Db.use("mysql").paginate(pageno, 20, "select c.wellcode,c.wellname,c.elevation,c.depth,c.totalthickness,c.netthickness,c.stratumpressure,c.SL,c.PLYL,c.PJSB,d.SCRQ,d.QQ,d.QL",sql,split[7]);
		}else if(split.length==12&&"".equals(split[3])&&"".equals(split[7])){
		       sql = "from (select a.wellcode,a.wellname,a.elevation,a.depth,a.totalthickness,a.netthickness,a.stratumpressure,b.SL,b.PLYL,b.PJSB from jtjcsj a inner join ylsgsj b on a.wellcode=b.wellcode ) c inner join pcdtsjtrue d on d."+split[9]+split[10]+"?  and c.wellcode=d.wellcode";
			    System.out.println(sql);
		       page = Db.use("mysql").paginate(pageno, 20, "select c.wellcode,c.wellname,c.elevation,c.depth,c.totalthickness,c.netthickness,c.stratumpressure,c.SL,c.PLYL,c.PJSB,d.SCRQ,d.QQ,d.QL",sql,split[11]);
		}else if(split.length==11&&!("".equals(split[3]))&&!("".equals(split[7]))){
		       sql = "from (select a.wellcode,a.wellname,a.elevation,a.depth,a.totalthickness,a.netthickness,a.stratumpressure,b.SL,b.PLYL,b.PJSB from jtjcsj a inner join ylsgsj b on a."+split[1]+split[2]+"? and b."+split[5]+split[6]+"? and a.wellcode=b.wellcode ) c inner join pcdtsjtrue d on c.wellcode=d.wellcode";
			    System.out.println(sql);
		       page = Db.use("mysql").paginate(pageno, 20, "select c.wellcode,c.wellname,c.elevation,c.depth,c.totalthickness,c.netthickness,c.stratumpressure,c.SL,c.PLYL,c.PJSB,d.SCRQ,d.QQ,d.QL",sql,split[3],split[7]);

		}else if(split.length==12&&!("".equals(split[3]))&&("".equals(split[7]))){
		       sql = "from (select a.wellcode,a.wellname,a.elevation,a.depth,a.totalthickness,a.netthickness,a.stratumpressure,b.SL,b.PLYL,b.PJSB from jtjcsj a inner join ylsgsj b on a."+split[1]+split[2]+"? and a.wellcode=b.wellcode ) c inner join pcdtsjtrue d on d."+split[9]+split[10]+"?  and c.wellcode=d.wellcode";
			   System.out.println(sql);
			   page = Db.use("mysql").paginate(pageno, 20, "select c.wellcode,c.wellname,c.elevation,c.depth,c.totalthickness,c.netthickness,c.stratumpressure,c.SL,c.PLYL,c.PJSB,d.SCRQ,d.QQ,d.QL",sql,split[3],split[11]);

		}else if(split.length==12&&("".equals(split[3]))&&!("".equals(split[7]))){
		       sql = "from (select a.wellcode,a.wellname,a.elevation,a.depth,a.totalthickness,a.netthickness,a.stratumpressure,b.SL,b.PLYL,b.PJSB from jtjcsj a inner join ylsgsj b on b."+split[5]+split[6]+"? and a.wellcode=b.wellcode ) c inner join pcdtsjtrue d on d."+split[9]+split[10]+"?  and c.wellcode=d.wellcode";
			   System.out.println(sql);
			   page = Db.use("mysql").paginate(pageno, 20, "select c.wellcode,c.wellname,c.elevation,c.depth,c.totalthickness,c.netthickness,c.stratumpressure,c.SL,c.PLYL,c.PJSB,d.SCRQ,d.QQ,d.QL",sql,split[7],split[11]);

		}else if(split.length==12&&!("".equals(split[3]))&&!("".equals(split[7]))){
	       sql = "from (select a.wellcode,a.wellname,a.elevation,a.depth,a.totalthickness,a.netthickness,a.stratumpressure,b.SL,b.PLYL,b.PJSB from jtjcsj a inner join ylsgsj b on a."+split[1]+split[2]+"? and b."+split[5]+split[6]+"? and a.wellcode=b.wellcode ) c inner join pcdtsjtrue d on d."+split[9]+split[10]+"?  and c.wellcode=d.wellcode";
	      System.out.println(sql);
	       page = Db.use("mysql").paginate(pageno, 20, "select c.wellcode,c.wellname,c.elevation,c.depth,c.totalthickness,c.netthickness,c.stratumpressure,c.SL,c.PLYL,c.PJSB,d.SCRQ,d.QQ,d.QL",sql,split[3],split[7],split[11]);
		}
		return page;
		
	}
//高级查询
	public List<Record> AdvanceQuery1(String para){
		  String[] split = para.split(",");
	      System.out.println(para);
	      int len=split.length;
	      System.out.println("参数个数"+len);
	      String sql=null;
	      Page<Record> list=null;
	      if(len==4){
	    	   sql = "from"+" "+split[0]+" "+"where"+" "+split[1]+split[2]+"?";
	    	   list=Db.use("mysql").paginate(1, 20, "select *", sql, split[3]);
	    	   System.out.println(sql);
	      }else if(len==8){
	    	  if(split[0].equals(split[3])){
	    		  sql="from"+" "+split[0]+" "+" where"+split[1]+split[2]+"?"+"and"+split[5]+split[6]+"?"; 
	    	  }else{
		    	   sql = " from"+" "+split[0]+" "+" a inner join"+" "+split[4]+" "+"b on a.wellcode=b.wellcode where a."+split[1]+split[2]+"?"+"and b."+split[5]+split[6]+"?"; 
	    	  }
	    	   list=Db.use("mysql").paginate(1,20,"select *",sql, split[3],split[7]);
	    	   System.out.println(sql);
	      }
	     
	 return list.getList();
	
}
	
	
}
